from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 1. 配置数据库连接
engine = create_engine('h2:///path/to/your/database.db', echo=True)  # 替换为你的数据库路径

# 2. 定义ORM模型
Base = declarative_base()


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    email = Column(String, unique=True, nullable=False)


# 3. 创建会话对象
Session = sessionmaker(bind=engine)

# 4. 执行查询
# 查询所有用户
with Session() as session:
    users = session.query(User).all()
    for user in users:
        print(f"ID: {user.id}, Name: {user.name}, Email: {user.email}")

# 或者，查询特定用户（例如，根据邮箱）
with Session() as session:
    email_to_find = 'example@example.com'  # 替换为你想要查询的邮箱
    user = session.query(User).filter(User.email == email_to_find).first()
    if user:
        print(f"Found user with ID: {user.id}, Name: {user.name}, Email: {user.email}")
    else:
        print("User not found.")
